import sqlite3
from openpyxl import load_workbook
import os


class ExcelToSQLiteMapper:
    def __init__(self, db_name="excel_data.db"):
        self.db_name = db_name
        self.conn = None
        self.setup_database()
    
    def setup_database(self):
        """设置数据库和表结构"""
        self.conn = sqlite3.connect(self.db_name)
        cursor = self.conn.cursor()
        
        # 创建映射表
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS name_mapping (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            excel_name TEXT NOT NULL,
            mapping_number INTEGER NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        ''')
        
        # 插入映射数据
        mapping_data = [
            ("龙三", 1),
            ("张三", 2),
            ("李四", 3),
            ("王五", 4),
            ("赵六", 5)
        ]
        
        cursor.executemany('''
        INSERT OR IGNORE INTO name_mapping (excel_name, mapping_number)
        VALUES (?, ?)
        ''', mapping_data)
        
        # 创建数据记录表
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS excel_records (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            cell_reference TEXT NOT NULL,
            selected_name TEXT NOT NULL,
            mapped_value INTEGER NOT NULL,
            import_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        ''')
        
        self.conn.commit()
    
    def map_to_database(self, excel_file, cell_reference='A1'):
        """将Excel数据映射到数据库"""
        try:
            wb = load_workbook(excel_file)
            ws = wb.active
            selected_name = ws[cell_reference].value
            
            if not selected_name:
                print("单元格为空")
                return None
            
            # 查询数据库获取映射值
            cursor = self.conn.cursor()
            cursor.execute('''
            SELECT mapping_number FROM name_mapping
            WHERE excel_name = ?
            ''', (selected_name,))
            
            result = cursor.fetchone()
            
            if result:
                mapped_value = result[0]
                
                # 记录到数据库
                cursor.execute('''
                INSERT INTO excel_records (cell_reference, selected_name, mapped_value)
                VALUES (?, ?, ?)
                ''', (cell_reference, selected_name, mapped_value))
                
                self.conn.commit()
                
                print(f"映射成功: {selected_name} → {mapped_value}")
                return mapped_value
            else:
                print(f"数据库中未找到映射: {selected_name}")
                return None
        
        except Exception as e:
            print(f"映射失败: {e}")
            return None
    
    def get_mapping_history(self):
        """获取映射历史记录"""
        cursor = self.conn.cursor()
        cursor.execute('''
        SELECT * FROM excel_records ORDER BY import_time DESC
        ''')
        return cursor.fetchall()
    
    def close(self):
        """关闭数据库连接"""
        if self.conn:
            self.conn.close()


# 使用示例
mapper = ExcelToSQLiteMapper()
mapped_value = mapper.map_to_database("dropdown_example.xlsx")

if mapped_value is not None:
    print(f"数据库中的映射值: {mapped_value}")
    
    # 查看历史记录
    history = mapper.get_mapping_history()
    print("\n映射历史:")
    for record in history:
        print(f"{record[2]} -> {record[3]} (时间: {record[4]})")

mapper.close()